package org.prophet.accessexcel.utility;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

/**
 * @Description: 读取Excel内容
 * @author: april
 * @date: 2020年10月07日 13:27
 */
public class ExcelReader {

    private static Logger logger = Logger.getLogger(ExcelReader.class.getName());

    private static final String SUFFIX_XLS = "xls";
    private static final String SUFFIX_XLSX = "xlsx";

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream
     * @param fileType    suffix
     * @return
     * @throws IOException
     */
    private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (SUFFIX_XLS.equalsIgnoreCase(fileType)) {
            workbook = new HSSFWorkbook(inputStream);
        }
        if (SUFFIX_XLSX.equalsIgnoreCase(fileType)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 根据提供的excel文件名返回workbook实例
     * @param fileName
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(String fileName) throws IOException {
        InputStream inputStream;

        String suffix = fileName.substring(fileName.indexOf(".") + 1, fileName.length());
        if(!SUFFIX_XLS.equalsIgnoreCase(suffix)&&!SUFFIX_XLSX.equalsIgnoreCase(suffix)){
            logger.warning("提供文件的非Excel类型文件！");
            return null;
        }

        // 获取Excel文件
        File excelFile = new File(fileName);
        if (!excelFile.exists()) {
            logger.warning("指定的Excel文件不存在！");
            return null;
        }
        inputStream = new FileInputStream(excelFile);
        return getWorkbook(inputStream, suffix);
    }

    /**
     * 读取Excel文件内容
     *
     * @return 读取结果列表，读取失败时返回null
     * @param1 fileName 要读取的Excel文件所在路径
     * @param2 entity 实力类实例
     */
    /*public static <T> List<T> readExcel(String fileName, Class<T> clazz) {
        List<T> results = null;
        Workbook workbook = null;
        InputStream inputStream;

        String suffix = fileName.substring(fileName.indexOf(".") + 1, fileName.length());
        // 获取Excel文件
        File excelFile = new File(fileName);
        if (!excelFile.exists()) {
            logger.warning("指定的Excel文件不存在！");
            return null;
        }

        // 获取Excel工作簿
        try {
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, suffix);
            if (workbook != null) {
                results = parseExcel(workbook, clazz);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return results;
    }*/

    /**
     * 返回excel中所有的sheet
     *
     * @param workbook
     * @return
     */
    public static List<Sheet> getSheets4Workbook(Workbook workbook) {
        List<Sheet> sheetList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            sheetList.add(sheet);
        }
        return sheetList;
    }

    /**
     * 根据提供的sheetIdx索引号返回对应的sheet
     *
     * @param workbook
     * @param sheetIdx
     * @return
     */
    public static Sheet getSheet4Workbook(Workbook workbook, int sheetIdx) {
        return workbook.getSheetAt(sheetIdx);
    }

    /**
     * 返回excel中索引号为0的sheet
     *
     * @param workbook
     * @return
     */
    public static Sheet getSheet4Workbook(Workbook workbook) {
        return workbook.getSheetAt(0);
    }


    /**
     * 解析Excel workbook,包含表中的多个sheet
     *
     * @return
     * @param1 workbook
     * @param2 clazz 实体类Class
     */
    /*private static <T> List<T> parseExcel(Workbook workbook, Class<T> clazz) {
        List<T> resultList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
            }
            //列名应该在每个sheet的第一行
            List<String> colNameList = new ArrayList<>();
            Iterator cellIterator = firstRow.cellIterator();
            while (cellIterator.hasNext()) {
                colNameList.add(cellIterator.next().toString());
            }
            if (0 == colNameList.size()) {
                logger.warning("解析Excel失败，无法从第一行获取字段名称！");
            }
            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (row == null) {
                    continue;
                }

                T resultData = null;
                try {
                    resultData = convertRowToData(row, clazz.newInstance());
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (null == resultData) {
                    logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                resultList.add(resultData);
            }
        }

        return resultList;
    }*/

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * <p>
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    public static <T> T convertRowToData(Row row, T entity) {

        Cell cell;
        int cellNum = 0;
        T result = null;
        //获取实体类的所有属性
        Field[] fields = entity.getClass().getDeclaredFields();
        Class<?> typeClass;
        for (int i = 0; i < fields.length; i++) {
            cell = row.getCell(cellNum++);
            try {
                typeClass = ReflectUtils.getFieldTypeClass(entity, fields[i].getName());
                ReflectUtils.setValue(entity, entity.getClass(), fields[i].getName(), typeClass
                        , convertCellValueToString(cell));

            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }
        result = entity;
        /*
        String name = convertCellValueToString(cell);
        resultData.setName(name);
        // 获取年龄
        cell = row.getCell(cellNum++);
        String ageStr = convertCellValueToString(cell);
        if (null == ageStr || "".equals(ageStr)) {
            // 年龄为空
            resultData.setAge(null);
        } else {
            resultData.setAge(Integer.parseInt(ageStr));
        }
        // 获取居住地
        cell = row.getCell(cellNum++);
        String location = convertCellValueToString(cell);
        resultData.setLocation(location);
        // 获取职业
        cell = row.getCell(cellNum++);
        String job = convertCellValueToString(cell);
        resultData.setJob(job);*/

        return result;
    }

}
